# -*- encoding: utf-8 -*-
from odoo import models, fields, api, _
from odoo.addons import base_cw
import base64
import os
from odoo.http import request
from odoo.exceptions import UserError, RedirectWarning, ValidationError
from odoo.tools.float_utils import float_round, float_compare
from collections import defaultdict
import io
import xlwt

VALUE_TYPE = [
    ('a1', U'当期(余额)'),
    ('b1', U'前一期(余额)'),
    ('c1', U'前二期(余额)'),
    ('d1', U'去年同期(余额)'),
    ('e1', U'年初(余额)'),
    ('f1', U'去年年初(余额)'),
    ('g1', U'年累(余额)'),
    ('h1', U'去年年累(余额)'),
    ('i1', U'当期(借-贷)'),
    ('j1', U'前一期(借-贷)'),
    ('k1', U'前二期(借-贷)'),
    ('l1', U'去年同期(借-贷)'),
    ('m1', U'年累(借-贷)'),
    ('n1', U'去年年累(借-贷)'),
    ('o1', U'前年年累(借-贷)'),]



class account_report_format_designer(models.Model):
    _name = 'account.report.format.designer'
    _description = u'报表格式设定'

    code = fields.Char(u'报表编号')
    name = fields.Char(u'报表名称', required=True)
    note = fields.Text(u'备注')
    active = fields.Boolean(u'启用', default=True)
    report_type = fields.Selection([('balance', u'资产负债表'),
                                    ('income', u'利润表'),
                                    ('cash', u'现金流量表'),
                                    ('tax', u'税金表'),
                                    ('tax_detail', u'应交税金明细表')],
                                   u'报表类型', required=True, default='balance')
    line_ids = fields.One2many('account.report.format.designer.line', 'master_id', u'报表项目')
    value_line_ids = fields.One2many('account.report.format.designer.value', 'master_id', u' 量值项目')

    balance_sheet_ids = fields.One2many('account.balance.sheet', 'master_id', u'资产负责表')
    income_statement_ids = fields.One2many('account.income.statement', 'master_id', u'利润表')
    cash_flows_ids = fields.One2many('account.cash.flows.statement', 'master_id', u'现金流量表')
    tax_detail_ids = fields.One2many('account.tax.report', 'master_id', u'应交税金明细表')

    period_id = fields.Many2one('account.period', u'期别')
    date_stop = fields.Date(u'截止日')
    date_settle = fields.Date(u'处理日')

    _sql_constraints = [('name_uniq', 'unique (name)', u'报表名称 不可重复!')]


    def action_open_import_format_designer_line_wizard(self):
        """
        打开 汇入明细窗口
        :return:
        """
        self.ensure_one()
        self._cr.commit()
        context = {}
        context.update(active_model=self._name,
                       active_ids=self.ids,
                       active_id=self.id,
                       )
        self.env.context = context
        self._cr.execute("delete from import_report_format_designer_line_wizard where create_uid=%s" % (self._uid,))
        wizard_id = self.env['import.report.format.designer.line_wizard'].create(dict(master_id=self.id))
        return wizard_id.wizard_view()


    def action_open_import_format_designer_wizard(self):
        """
        打开 汇入明细窗口
        :return:
        """
        self.ensure_one()
        self._cr.commit()
        context = {}
        context.update(active_model=self._name,
                       active_ids=self.ids,
                       active_id=self.id,
                       )
        self.env.context = context
        self._cr.execute("delete from import_report_format_designer_wizard where create_uid=%s" % (self._uid,))
        wizard_id = self.env['import.report.format.designer.wizard'].create(dict(master_id=self.id))
        return wizard_id.wizard_view()


    def action_open_account_period_wizard(self):
        """
        打开 期别窗口
        :return:
        """
        self.ensure_one()
        self._cr.commit()
        context = {}
        context.update(active_model=self._name,
                       active_ids=self.ids,
                       active_id=self.id,
                       period_id=self.period_id.id
                       )
        self.env.context = context
        self._cr.execute("delete from account_period_wizard where create_uid=%s" % (self._uid,))
        wizard_id = self.env['account.period.wizard'].create(dict(active_id=self.id))
        return wizard_id.wizard_view()


    def action_out_to_excel(self):
        self.ensure_one()
        book = xlwt.Workbook(encoding='utf-8', style_compression=0)
        sheet1 = book.add_sheet('Sheet1')
        sheet1_header = ['name', 'report_type', 'line_ids/sequence', 'line_ids/name', 'line_ids/print_sequence',
                         'line_ids/note', 'line_ids/line_ids/sequence', 'line_ids/line_ids/operator',
                         'line_ids/line_ids/operator_int', 'line_ids/line_ids/amount_from',
                         'line_ids/line_ids/value_type', 'line_ids/line_ids/account_id',
                         'line_ids/line_ids/report_line_id']
        col = 0
        for x in sheet1_header:
            sheet1.write(0, col, x)
            col += 1
        row = 1
        for line in self.line_ids:
            sheet1.write(row, 0, self.name.encode('utf-8').decode('utf-8'))
            sheet1.write(row, 1, self.report_type.encode('utf-8').decode('utf-8'))
            sheet1.write(row, 2, line.sequence)
            sheet1.write(row, 3, line.name.encode('utf-8').decode('utf-8') if line.name else '')
            sheet1.write(row, 4, line.print_sequence)
            sheet1.write(row, 5, line.note.encode('utf-8').decode('utf-8') if line.note else '')

            dd = 1
            for line_dd in line.line_ids:
                if dd > 1:
                    sheet1.write(row, 0, self.name)
                    sheet1.write(row, 1, self.report_type)
                    sheet1.write(row, 2, line.sequence)
                    sheet1.write(row, 3, line.name if line.name else '')
                    sheet1.write(row, 4, line.print_sequence)
                    sheet1.write(row, 5, line.note if line.name else '')
                sheet1.write(row, 6, line_dd.sequence)
                sheet1.write(row, 7, line_dd.operator)
                sheet1.write(row, 8, line_dd.operator_int)
                sheet1.write(row, 9, line_dd.amount_from)
                sheet1.write(row, 10, line_dd.value_type)
                if line_dd.account_id:
                    sheet1.write(row, 11, line_dd.account_id.code)
                if line_dd.report_line_id:
                    sheet1.write(row, 12, line_dd.report_line_id.sequence)
                row += 1
                dd += 1
            if len(line.line_ids) == 0:
                row += 1

        sheet2 = book.add_sheet('Sheet2')
        sheet2_header = ['name', 'report_type', 'value_line_ids/sequence', 'value_line_ids/value_type',
                         'value_line_ids/name']
        col = 0
        for x in sheet2_header:
            sheet2.write(0, col, x)
            col += 1
        row = 1
        for line in self.value_line_ids:
            sheet2.write(row, 0, self.name.encode('utf-8').decode('utf-8'))
            sheet2.write(row, 1, self.report_type.encode('utf-8').decode('utf-8'))
            sheet2.write(row, 2, line.sequence)
            sheet2.write(row, 3, line.value_type.encode('utf-8').decode('utf-8'))
            sheet2.write(row, 4, line.name.encode('utf-8').decode('utf-8'))
            row += 1
        book.save('d:\\' + self.name + '.xlsx')

    # def export_excel(self):
    #     """导出excel"""
    #     for record in self:
    #         return self.env['report'].get_action(record, 'designer_export_excel', data=record.id)

    def report_printing(self):
        """报表 打印"""
        book = xlwt.Workbook(encoding='utf-8', style_compression=0)
        style = xlwt.easyxf(
            'font: height 150;'
            'alignment: vert center, horizontal center;'
            'borders: left thin, right thin, top thin, bottom thin;'
        )

        header_style = xlwt.easyxf(
            'font: height 150;'
            'borders: left thin, right thin, top thin, bottom thin;'
            'align: vertical center, horizontal center;'
        )
        filename = ''
        if self.report_type == 'balance':
            filename,sheet1 = self.balance_report_out_to_excel(book,style,header_style)
        if self.report_type == 'income':
            filename, sheet1 = self.income_report_out_to_excel(book,style,header_style)
        if self.report_type == 'cash':
            filename, sheet1 = self.cash_report_out_to_excel(book,style,header_style)
        if self.report_type in ['tax','tax_detail']:
            filename, sheet1 = self.tax_report_out_to_excel(book,style,header_style)
        if filename:
            general_ledger_path = os.path.abspath(os.path.dirname(os.path.dirname(__file__)))
            # 若不存在则创建
            if not os.path.exists('%s/static/tmp' % general_ledger_path):
                os.makedirs('%s/static/tmp' % general_ledger_path)
            book.save('%s/static/tmp/%s.xls' % (general_ledger_path,filename))
            result_file = open('%s/static/tmp/%s.xls' % (general_ledger_path,filename), 'rb').read()
            # fp.seek(0)
            # content = fp.read()

            attachment_id = self.env['wizard.account.designer.excel.report'].create({
                'name': '%s.xls'%filename,
                'file_name': '%s.xls' % filename,
                'report': base64.b64encode(result_file)
            })

            return {
                'name': _('报表下载'),
                'context': self.env.context,
                'view_type': 'form',
                'view_mode': 'form',
                'res_model': 'wizard.account.designer.excel.report',
                'res_id': attachment_id.id,
                'data': None,
                'type': 'ir.actions.act_window',
                'target': 'new'
            }

    def balance_report_out_to_excel(self, book,style,header_style):
        """生成资产负债表的excel"""
        sheet1 = book.add_sheet('资产负债表')
        # 第一行标题
        sheet1.write_merge(0, 0, 0, 5, '%s资产负债报表'%self.period_id.name, style=header_style)
        #第二行
        sheet1_header = ['资产', '期末余额', '年初余额', '负债及权益类', '期末余额', '年初余额']
        [sheet1.write(1, num, line, header_style) for num, line in enumerate(sheet1_header)]
        for num, line in enumerate(self.balance_sheet_ids):
            sheet1.write(num + 2, 0, line.asset_name if line.asset_name else '', style)
            sheet1.write(num + 2, 1, line.asset_begin, style)
            sheet1.write(num + 2, 2, line.asset_balance, style)
            sheet1.write(num + 2, 3, line.liability_name if line.liability_name else '', style)
            sheet1.write(num + 2, 4, line.liability_begin, style)
            sheet1.write(num + 2, 5, line.liability_balance, style)
        return 'balance_report', sheet1

    def income_report_out_to_excel(self,book,style,header_style):
        """生成利润表"""
        sheet1 = book.add_sheet('利润表')
        # 第一行标题
        sheet1.write_merge(0, 0, 0, 6, '%s利润表' % self.period_id.name, style=header_style)
        # 第二行
        sheet1_header = ['项次', '项目', '本年累计数', '本月数', '税别项目', '期末账面数','年初账面数']
        [sheet1.write(1, num, line, header_style) for num, line in enumerate(sheet1_header)]
        for line in self.income_statement_ids:
            sheet1.write(int(line.print_sequence) + 2, 0, line.sequence, style)
            sheet1.write(int(line.print_sequence) + 2, 1, line.name, style)
            sheet1.write(int(line.print_sequence) + 2, 2, line.year_balance, style)
            sheet1.write(int(line.print_sequence) + 2, 3, line.balance, style)
            sheet1.write(int(line.print_sequence) + 2, 4, line.tax_name if line.tax_name else '', style)
            sheet1.write(int(line.print_sequence) + 2, 5, line.tax_month, style)
            sheet1.write(int(line.print_sequence) + 2, 6, line.tax_year, style)
        return 'depart_quality_report',sheet1

    def cash_report_out_to_excel(self,book,style,header_style):
        """生成现金流量表"""
        sheet1 = book.add_sheet('现金流量表')
        # 第一行标题
        sheet1.write_merge(0, 0, 0, 3, '%s现金流量表' % self.period_id.name, style=header_style)
        # 第二行
        sheet1_header = ['项次', '项目',  '本年累计数','本月数']
        [sheet1.write(1, num, line, header_style) for num, line in enumerate(sheet1_header)]
        for num, line in enumerate(self.cash_flows_ids):
            sheet1.write(num + 2, 0, line.sequence, style)
            sheet1.write(num + 2, 1, line.name, style)
            sheet1.write(num + 2, 2, line.year_balance, style)
            sheet1.write(num + 2, 3, line.balance, style)
        return 'cash_report', sheet1

    def tax_report_out_to_excel(self,book,style,header_style):
        """生成税金表"""

        if self.report_type == 'tax':
            sheet_name = '税金表'
            filename = 'tax_report'
        else:
            sheet_name = '应交税金明细表'
            filename = 'tax_detail_report'
        sheet1 = book.add_sheet(sheet_name)
        # 第一行标题
        sheet1.write_merge(0, 0, 0, 3, '%s%s' % (self.period_id.name,sheet_name), style=header_style)
        # 第二行
        sheet1_header = ['项次', '项目',  '本年累计数','本月数']
        [sheet1.write(1, num, line, header_style) for num, line in enumerate(sheet1_header)]
        for line in self.tax_detail_ids:
            sheet1.write(int(line.print_sequence) + 2, 0, line.sequence, style)
            sheet1.write(int(line.print_sequence) + 2, 1, line.name, style)
            sheet1.write(int(line.print_sequence) + 2, 2, line.year_balance, style)
            sheet1.write(int(line.print_sequence) + 2, 3, line.balance, style)
        return filename,sheet1


class account_report_format_designer_line(models.Model):
    _name = 'account.report.format.designer.line'
    _description = u'报表格式项目明细'


    @api.depends('sequence', 'name')
    def name_get(self):
        result = []
        for x in self:
            name = '%s - %s' % (x.sequence, x.name)
            result.append((x.id, name))
        return result

    @api.model
    def name_search(self, name, args=None, operator='ilike', limit=100):
        args = args or []
        recs = self.browse()
        if name:
            recs = self.search(['|', ('name', operator, name), ('sequence', operator, name)] + args, limit=limit)
        if not recs:
            recs = self.search([('name', operator, name)] + args, limit=limit)
        return recs.name_get()

    def _compute_report_line_ids(self):
        for record in self:
            if not record.report_line_ids:
                record.report_line_ids = record.master_id.line_ids.filtered(lambda x: x.id != record.id).ids

    master_id = fields.Many2one('account.report.format.designer', u'报表格式设定', ondelete="cascade")
    line_ids = fields.One2many('account.report.format.designer.line.dd', 'master_id', u'报表项目数据来源明细')
    sequence = fields.Integer(u'项次', default=1)
    name = fields.Char(u'报表项目', required=False)
    print_sequence = fields.Char(u'报表行次')
    note = fields.Text(u'说明')
    itemtype = fields.Selection([('none', '无'), ('asset', '资产类项目'), ('liability', '负债及所有者权益项目')], string=u'项目类型',
                                default='none')

    llc = fields.Integer(u'阶层', default=1)
    partner_id = fields.Many2one('res.partner', u'辅助核算', ondelete="set null")
    percent_base_id = fields.Many2one('account.report.format.designer.line', u'比重基础', ondelete="set null")
    report_line_ids = fields.Many2many('account.report.format.designer.line', u'可选报表项目'
                                       , compute='_compute_report_line_ids')
    write_uid = fields.Many2one('res.users', u'处理人', readonly=True)
    write_date = fields.Datetime(u'处理时间', readonly=True)

    a_name = fields.Char(u'当期(余额)')
    b_name = fields.Char(u'前一期(余额)')
    c_name = fields.Char(u'前二期(余额)')
    d_name = fields.Char(u'去年同期(余额)')
    e_name = fields.Char(u'年初(余额)')
    f_name = fields.Char(u'去年年初(余额)')
    g_name = fields.Char(u'年累(余额)')
    h_name = fields.Char(u'去年年累(余额)')
    i_name = fields.Char(u'当期(借-贷)')
    j_name = fields.Char(u'前一期(借-贷)')
    k_name = fields.Char(u'前二期(借-贷)')
    l_name = fields.Char(u'去年同期(借-贷)')
    m_name = fields.Char(u'年累(借-贷)')
    n_name = fields.Char(u'去年年累(借-贷)')
    o_name = fields.Char(u'前年年累(借-贷)')

    a1 = fields.Float(u'当期(余额)', digits='Amount')
    b1 = fields.Float(u'前一期(余额)', digits='Amount')
    c1 = fields.Float(u'前二期(余额)', digits='Amount')
    d1 = fields.Float(u'去年同期(余额)', digits='Amount')
    p1 = fields.Float(u'前年同期(余额)', digits='Amount')

    e1 = fields.Float(u'年初余额', digits='Amount')
    f1 = fields.Float(u'去年年初余额', digits='Amount')
    s1 = fields.Float(u'前年年初余额', digits='Amount')

    g1 = fields.Float(u'年累余额', digits='Amount')
    h1 = fields.Float(u'去年同期年累余额', digits='Amount')
    q1 = fields.Float(u'前年同期年累余额', digits='Amount')

    i1 = fields.Float(u'当期(借-贷)', digits='Amount')
    j1 = fields.Float(u'前一期(借-贷)', digits='Amount')
    k1 = fields.Float(u'前二期(借-贷)', digits='Amount')
    l1 = fields.Float(u'去年同期(借-贷)', digits='Amount')
    r1 = fields.Float(u'前年同期(借-贷)', digits='Amount')

    m1 = fields.Float(u'年累(借-贷)', digits='Amount')
    n1 = fields.Float(u'去年同期年累(借-贷)', digits='Amount')
    o1 = fields.Float(u'前年同期年累(借-贷)', digits='Amount')

    t1 = fields.Float(u'年累借', digits='Amount')
    u1 = fields.Float(u'年累贷', digits='Amount')

    a2 = fields.Float(u'当期(余额)%', digits='Amount')
    b2 = fields.Float(u'前一期(余额)%', digits='Amount')
    c2 = fields.Float(u'前二期(余额)%', digits='Amount')
    d2 = fields.Float(u'去年同期(余额)%', digits='Amount')
    e2 = fields.Float(u'年初(余额)%', digits='Amount')
    f2 = fields.Float(u'去年年初(余额)%', digits='Amount')
    g2 = fields.Float(u'年累(余额)%', digits='Amount')
    h2 = fields.Float(u'去年年累(余额)%', digits='Amount')
    i2 = fields.Float(u'当期(借-贷)%', digits='Amount')
    j2 = fields.Float(u'前一期(借-贷)%', digits='Amount')
    k2 = fields.Float(u'前二期(借-贷)%', digits='Amount')
    l2 = fields.Float(u'去年同期(借-贷)%', digits='Amount')
    m2 = fields.Float(u'年累(借-贷)%', digits='Amount')
    n2 = fields.Float(u'去年年累(借-贷)%', digits='Amount')
    o2 = fields.Float(u'前年年累(借-贷)%', digits='Amount')

    a3 = fields.Float(u'当期(余额)比较', digits='Amount')
    b3 = fields.Float(u'前一期(余额)比较', digits='Amount')
    c3 = fields.Float(u'前二期(余额)比较', digits='Amount')
    d3 = fields.Float(u'去年同期(余额)比较', digits='Amount')
    e3 = fields.Float(u'年初(余额)比较', digits='Amount')
    f3 = fields.Float(u'去年年初(余额)比较', digits='Amount')
    g3 = fields.Float(u'年累(余额)比较', digits='Amount')
    h3 = fields.Float(u'去年年累(余额)比较', digits='Amount')
    i3 = fields.Float(u'当期(借-贷)比较', digits='Amount')
    j3 = fields.Float(u'前一期(借-贷)比较', digits='Amount')
    k3 = fields.Float(u'前二期(借-贷)比较', digits='Amount')
    l3 = fields.Float(u'去年同期(借-贷)比较', digits='Amount')
    m3 = fields.Float(u'年累(借-贷)比较', digits='Amount')
    n3 = fields.Float(u'去年年累(借-贷)比较', digits='Amount')
    o3 = fields.Float(u'前年年累(借-贷)比较', digits='Amount')

    @api.model
    def create(self, vals):
        base_cw.public.generate_sequence(self, vals)
        return super(account_report_format_designer_line, self).create(vals)

    @api.model
    def default_get(self, fields):
        if self._context is None:
            self._context = {}
        res = super(account_report_format_designer_line, self).default_get(fields)
        active_id = self._context.get('default_active_id', False)
        if active_id:
            res.update(report_line_ids=self.env['account.report.format.designer'].browse(active_id).line_ids.ids)
        return res


class account_report_format_designer_line_dd(models.Model):
    _name = 'account.report.format.designer.line.dd'
    _description = u'报表项目数据来源明细'

    master_id = fields.Many2one('account.report.format.designer.line', u'报表格式项目明细', ondelete="set null")
    report_id = fields.Many2one('account.report.format.designer', u'报表格式设定', ondelete="set null")
    sequence = fields.Integer(u'项次', default=1)
    operator = fields.Selection([('plus', u'+'), ('minus', u'-')]
                                , u'运算符',
                                required=True, default='plus')
    operator_int = fields.Integer(u'运算符', default=1)
    amount_from = fields.Selection([('account', u'会科'),
                                    ('report_line', u'报表项目'),
                                    ('subaccount', u'报表项目对应辅助核算'),  # 仅现金流表使用
                                    ], u'取值来源',
                                   required=True, default='account')
    value_type = fields.Selection([('begin', u'期初'),
                                   ('debit', u'借方'),
                                   ('credit', u'贷方'),
                                   ('debit_credit', u'借-贷'),
                                   ('a1', u'期末'),
                                   ], u'量值', required=True, default='a1')
    # amount_type = fields.Selection([('1', u'年初余额'),
    #                                 ('2', u'期初余额'),
    #                                 ('3', u'本期借方'),
    #                                 ('4', u'本期贷方'),
    #                                 ('5', u'期末余额'),
    #                                 ('6', u'借方年累'),
    #                                 ('7', u'贷方年累'),
    #                                 ], u'取值类型',
    #                                 required=True, default='5')
    account_id = fields.Many2one('cncw.account', u'会科', ondelete="set null")
    report_line_id = fields.Many2one('account.report.format.designer.line', u'报表项目', ondelete="set null")
    # report_line_ids = fields.Many2many('account.report.format.designer.line', u'可选报表项目'
    #                                    , compute='_compute_report_line_ids')
    note = fields.Text(u'说明')
    begin = fields.Float(u'期初金额)', digits='Amount')
    debit = fields.Float(u'本期借方', digits='Amount')
    credit = fields.Float(u'本期贷方', digits='Amount')

    a1 = fields.Float(u'当期(余额)', digits='Amount')
    b1 = fields.Float(u'前一期(余额)', digits='Amount')
    c1 = fields.Float(u'前二期(余额)', digits='Amount')
    d1 = fields.Float(u'去年同期(余额)', digits='Amount')
    p1 = fields.Float(u'前年同期(余额)', digits='Amount')

    e1 = fields.Float(u'年初余额', digits='Amount')
    f1 = fields.Float(u'去年年初余额', digits='Amount')
    s1 = fields.Float(u'前年年初余额', digits='Amount')

    g1 = fields.Float(u'年累余额', digits='Amount')
    h1 = fields.Float(u'去年同期年累余额', digits='Amount')
    q1 = fields.Float(u'前年同期年累余额', digits='Amount')

    i1 = fields.Float(u'当期(借-贷)', digits='Amount')
    j1 = fields.Float(u'前一期(借-贷)', digits='Amount')
    k1 = fields.Float(u'前二期(借-贷)', digits='Amount')
    l1 = fields.Float(u'去年同期(借-贷)', digits='Amount')
    r1 = fields.Float(u'前年同期(借-贷)', digits='Amount')

    m1 = fields.Float(u'年累(借-贷)', digits='Amount')
    n1 = fields.Float(u'去年同期年累(借-贷)', digits='Amount')
    o1 = fields.Float(u'前年同期年累(借-贷)', digits='Amount')

    t1 = fields.Float(u'年累借', digits='Amount')
    u1 = fields.Float(u'年累贷', digits='Amount')

    @api.model
    def create(self, vals):
        base_cw.public.generate_sequence(self, vals)
        return super(account_report_format_designer_line_dd, self).create(vals)

    @api.onchange('operator')
    def onchange_operator(self):
        if self.operator == 'minus':
            self.operator_int = -1
        else:
            self.operator_int = 1

    @api.onchange('amount_from')
    def onchange_data_from(self):
        if self.amount_from == 'account':
            self.report_line_id = False
        elif self.amount_from == 'report_line':
            self.account_id = False
        else:
            self.value_type = 'debit_credit'
            self.report_line_id = False
            self.account_id = False

    @api.model
    def default_get(self, fields):
        if self._context is None:
            self._context = {}
        res = super(account_report_format_designer_line_dd, self).default_get(fields)
        active_id = self._context.get('default_active_id', False)
        if active_id:
            ids = self.env['account.report.format.designer.line'].browse(active_id)._compute_report_line_ids()
            res.update(value_ids=ids)
        # parent_id = self._context.get('default_parent_id', False)
        # if parent_id:
        #     ids = self.env['account.report.format.designer'].browse(parent_id).line_ids.ids
        #     pass
        return res


class account_report_format_designer_value(models.Model):
    _name = 'account.report.format.designer.value'
    _description = u'报表格式量值项目'

    def _compute_value_ids(self):
        if not self.value_ids:
            self.value_ids = self.master_id.value_line_ids.filtered(
                lambda x: x.id != self.id and x.value_type[-1] == '1').ids

    @api.depends('value_type')
    def _compute_type(self):
        if self.value_type:
            self.type = self.value_type[-1]

    master_id = fields.Many2one('account.report.format.designer', u'报表格式', ondelete="cascade")
    line_ids = fields.One2many('account.report.format.designer.value.dd', 'master_id', u'报表格式量值明细')
    sequence = fields.Integer(u'项次', default=1)
    value_type = fields.Selection(VALUE_TYPE, u'量值', required=True, default='a1')
    name = fields.Char(u'打印标题', default=False)
    type = fields.Char(u'Type', compute='_compute_type')
    value_ids = fields.Many2many('account.report.format.designer.value', u'可选报表项目'
                                 , compute='_compute_value_ids')

    @api.model
    def create(self, vals):
        base_cw.public.generate_sequence(self, vals)
        return super(account_report_format_designer_value, self).create(vals)

    @api.onchange('value_type')
    def onchange_value_type(self):
        if self.value_type:
            self.name = dict(VALUE_TYPE)[self.value_type]

    @api.model
    def default_get(self, fields):
        if self._context is None:
            self._context = {}
        res = super(account_report_format_designer_value, self).default_get(fields)
        active_id = self._context.get('default_active_id', False)
        if active_id:
            res.update(value_ids=self.env['account.report.format.designer'].browse(active_id).value_line_ids.ids)
        return res


class account_report_format_designer_value_dd(models.Model):
    _name = 'account.report.format.designer.value.dd'
    _description = u'报表格式量值明细'

    master_id = fields.Many2one('account.report.format.designer.value', u'量值项目', ondelete="cascade")
    sequence = fields.Integer(u'项次', default=1)
    operator = fields.Selection([('plus', u'+'), ('minus', u'-'), ]
                                , u'运算符',
                                required=True, default='plus')
    operator_int = fields.Integer(u'运算符', default=1)
    value_id = fields.Many2one('account.report.format.designer.value', u'量值项目', ondelete="restrict")

    @api.model
    def create(self, vals):
        base_cw.public.generate_sequence(self, vals)
        return super(account_report_format_designer_value_dd, self).create(vals)

    @api.onchange('operator')
    def onchange_operator(self):
        if self.operator == 'minus':
            self.operator_int = -1
        else:
            self.operator_int = 1

    @api.model
    def default_get(self, fields):
        if self._context is None:
            self._context = {}
        res = super(account_report_format_designer_value_dd, self).default_get(fields)
        active_id = self._context.get('default_active_id', False)
        if active_id:
            ids = self.env['account.report.format.designer.value'].browse(active_id)._compute_value_ids()
            res.update(value_ids=ids)
        # parent_id = self._context.get('default_parent_id', False)
        # if parent_id:
        #     ids = self.env['account.report.format.designer'].browse(parent_id).value_line_ids.ids
        #     pass
        return res

    def init(self):
        self._cr.execute("""
CREATE OR REPLACE FUNCTION account_report_settle(period_id_t integer,
					    master_id_t integer,
					    uid_t integer)
  RETURNS void AS
  $BODY$
  --财务报表计算
  --计算步骤1.先将相关值从 会科余额表中取值到 account_report_format_designer_line_dd
  --        2.逐阶汇总
-- select account_report_settle(9,1,1)
-- select account_report_settle(10,2,1)
-- select account_report_settle(9,3,1)
-- select account_report_settle(9,4,1)
-- select account_report_settle(10,5,1)
-- select * from account_report_format_designer
-- drop function account_report_settle(integer,integer,integer)
declare
      col_t character varying;
      col_1 character varying;
      col_multi character varying;
      name_t  character varying;
      con_name  character varying;
      sql character varying;
      rec record;
      rec_dd record;
      end_date_t timestamp;
      report_type_t  character varying;
      tax_master_id_t integer;
begin
        update account_report_format_designer_line_dd a set a1=0, b1=0, c1=0, d1=0, e1=0, f1=0, g1=0, h1=0, i1=0, j1=0, k1=0, l1=0, m1=0,n1=0, o1=0,
                                                            begin=0,credit=0,debit=0,t1=0,u1=0
          from  account_report_format_designer_line b left join account_report_format_designer c on b.master_id=c.id
	 where a.master_id=b.id
           and c.id=master_id_t;

        update account_report_format_designer_line a set a1=0, b1=0, c1=0, d1=0, e1=0, f1=0, g1=0, h1=0, i1=0, j1=0, k1=0, l1=0, m1=0, n1=0, o1=0
                                                         ,a2=0, b2=0, c2=0, d2=0, e2=0, f2=0, g2=0, h2=0, i2=0, j2=0, k2=0, l2=0, m2=0, n2=0, o2=0
                                                         ,a3=0, b3=0, c3=0, d3=0, e3=0, f3=0, g3=0, h3=0, i3=0, j3=0, k3=0, l3=0, m3=0, n3=0, o3=0
                                                         ,t1=0,u1=0
                                                         ,a_name=null, b_name=null, c_name=null, d_name=null, e_name=null, f_name=null, g_name=null, h_name=null, i_name=null, j_name=null, k_name=null, l_name=null, m_name=null, n_name=null, o_name=null
	 where a.master_id=master_id_t;

        --1.更新第三阶 取值于会科的项目，
	update account_report_format_designer_line_dd a set  begin=coalesce(d.begin,0),
	                                                     credit=coalesce(d.credit,0),
							     debit=coalesce(d.debit,0),
	                                                     a1=coalesce(d.a1,0),
							     b1=coalesce(d.b1,0),
							     c1=coalesce(d.c1,0),
							     d1=coalesce(d.d1,0),
							     e1=coalesce(d.e1,0),
							     f1=coalesce(d.f1,0),
							     g1=coalesce(d.g1,0),
							     h1=coalesce(d.h1,0),
							     i1=coalesce(d.i1,0),
							     j1=coalesce(d.j1,0),
							     k1=coalesce(d.k1,0),
							     l1=coalesce(d.l1,0),
							     m1=coalesce(d.m1,0),
							     n1=coalesce(d.n1,0),
							     o1=coalesce(d.o1,0),
							     t1=coalesce(d.t1,0),
							     u1=coalesce(d.u1,0)
	  from  account_report_format_designer_line b left join account_report_format_designer c on b.master_id=c.id
		,(select * from account_balance where period_id=period_id_t and sub_account_id is null) d
	 where a.master_id=b.id
	   and a.account_id is not null
	   and a.amount_from ='account'
	   and c.id=master_id_t
	   and a.account_id=d.account_id;
	   PERFORM compute_report_llc(master_id_t);

        --利润表 begin  借用 a1 和 g1 栏位 计算利润表   取值本期借、贷 或 年累借、贷
	for rec in select a.* from  account_report_format_designer_value a left join account_report_format_designer b on a.master_id=b.id
	            where master_id=master_id_t and b.report_type='income'
	loop
           if rec.value_type='a1' then
              update account_report_format_designer_line_dd a set a1=a.debit
                from account_report_format_designer_line b left join account_report_format_designer c on b.master_id=c.id
               where a.master_id=b.id
		 and a.account_id is not null
		 and a.amount_from ='account'
		 and a.value_type='debit'
		 and c.id=master_id_t;

              update account_report_format_designer_line_dd a set a1=a.credit
                from account_report_format_designer_line b left join account_report_format_designer c on b.master_id=c.id
               where a.master_id=b.id
		 and a.account_id is not null
		 and a.amount_from ='account'
		 and a.value_type='credit'
		 and c.id=master_id_t;
           end if;

           if rec.value_type='g1' then--年累余额
              update account_report_format_designer_line_dd a set g1=a.t1--年累借
                from account_report_format_designer_line b left join account_report_format_designer c on b.master_id=c.id
               where a.master_id=b.id
		 and a.account_id is not null
		 and a.amount_from ='account'
		 and a.value_type='debit'
		 and c.id=master_id_t;

              update account_report_format_designer_line_dd a set g1=a.u1--年累贷
                from account_report_format_designer_line b left join account_report_format_designer c on b.master_id=c.id
               where a.master_id=b.id
		 and a.account_id is not null
		 and a.amount_from ='account'
		 and a.value_type='credit'
		 and c.id=master_id_t;
           end if;
	end loop;
       --利润表 end

       --应交税金明细表 begin
	for rec in select a.* from  account_report_format_designer_value a left join account_report_format_designer b on a.master_id=b.id
	            where master_id=master_id_t and b.report_type='tax_detail'
	loop
   if rec.value_type='a1' then
      update account_report_format_designer_line_dd a set a1=a.begin
        from account_report_format_designer_line b left join account_report_format_designer c on b.master_id=c.id
       where a.master_id=b.id
		 and a.account_id is not null
		 and a.amount_from ='account'
		 and a.value_type='begin'
		 and c.id=master_id_t;

      update account_report_format_designer_line_dd a set a1=a.debit
        from account_report_format_designer_line b left join account_report_format_designer c on b.master_id=c.id
       where a.master_id=b.id
		 and a.account_id is not null
		 and a.amount_from ='account'
		 and a.value_type='debit'
		 and c.id=master_id_t;

      update account_report_format_designer_line_dd a set a1=a.credit
        from account_report_format_designer_line b left join account_report_format_designer c on b.master_id=c.id
       where a.master_id=b.id
		 and a.account_id is not null
		 and a.amount_from ='account'
		 and a.value_type='credit'
		 and c.id=master_id_t;
           end if;
   if rec.value_type='g1' then
      update account_report_format_designer_line_dd a set g1=a.e1
        from account_report_format_designer_line b left join account_report_format_designer c on b.master_id=c.id
       where a.master_id=b.id
		 and a.account_id is not null
		 and a.amount_from ='account'
		 and a.value_type='begin'
		 and c.id=master_id_t;

      update account_report_format_designer_line_dd a set g1=a.t1
        from account_report_format_designer_line b left join account_report_format_designer c on b.master_id=c.id
       where a.master_id=b.id
		 and a.account_id is not null
		 and a.amount_from ='account'
		 and a.value_type='debit'
		 and c.id=master_id_t;

      update account_report_format_designer_line_dd a set g1=a.u1
        from account_report_format_designer_line b left join account_report_format_designer c on b.master_id=c.id
       where a.master_id=b.id
		 and a.account_id is not null
		 and a.amount_from ='account'
		 and a.value_type='credit'
		 and c.id=master_id_t;

      update account_report_format_designer_line_dd a set g1=a.a1
        from account_report_format_designer_line b left join account_report_format_designer c on b.master_id=c.id
       where a.master_id=b.id
		 and a.account_id is not null
		 and a.amount_from ='account'
		 and a.value_type='a1'
		 and c.id=master_id_t;
    end if;

	end loop;
	--应交税金明细表 end

	---现金流表 begin  取现金流表项目 本期发生数 借-贷
	update account_report_format_designer_line_dd a set  a1=coalesce(d.a1,0),
							     b1=coalesce(d.b1,0),
							     c1=coalesce(d.c1,0),
							     d1=coalesce(d.d1,0),
							     e1=coalesce(d.e1,0),
							     f1=coalesce(d.f1,0),
							     g1=coalesce(d.g1,0),
							     h1=coalesce(d.h1,0),
							     i1=coalesce(d.i1,0),
							     j1=coalesce(d.j1,0),
							     k1=coalesce(d.k1,0),
							     l1=coalesce(d.l1,0),
							     m1=coalesce(d.m1,0),
							     n1=coalesce(d.n1,0),
							     o1=coalesce(d.o1,0)
	  from  account_report_format_designer_line b left join account_report_format_designer c on b.master_id=c.id
		,(select * from account_balance where period_id=period_id_t and sub_account_id  is not null and account_id  is null) d
	 where a.master_id=b.id
	   and a.account_id is null
	   and a.amount_from ='subaccount'
	   and a.value_type ='debit_credit'
	   and c.id=master_id_t
	   and b.partner_id=d.sub_account_id
	   and b.partner_id is not null
	   and c.report_type='cash'; --现金流表

        update account_report_format_designer_line_dd a set i1=a.begin*(operator_int),--期初
                                                            m1=a.e1*(operator_int) --年初
	  from account_report_format_designer_line b left join account_report_format_designer c on b.master_id=c.id
	 where a.master_id=b.id
	   and a.account_id is not null
	   and a.amount_from ='account'
	   and a.value_type ='begin'
	   and c.id=master_id_t
	   and c.report_type='cash'; --现金流表
       -- 现金流表end

        --2.计算各报表项目值
	for rec in select distinct coalesce(llc,0) as llc from account_report_format_designer_line order by coalesce(llc,0) desc
	loop
		 update account_report_format_designer_line a set a1=coalesce(d.a1,0),
								     b1=coalesce(d.b1,0),
								     c1=coalesce(d.c1,0),
								     d1=coalesce(d.d1,0),
								     e1=coalesce(d.e1,0),
								     f1=coalesce(d.f1,0),
								     g1=coalesce(d.g1,0),
								     h1=coalesce(d.h1,0),
								     i1=coalesce(d.i1,0),
								     j1=coalesce(d.j1,0),
								     k1=coalesce(d.k1,0),
								     l1=coalesce(d.l1,0),
								     m1=coalesce(d.m1,0),
								     n1=coalesce(d.n1,0),
								     o1=coalesce(d.o1,0)
		  from (select ee.master_id,sum(operator_int*coalesce(ee.a1,0)) as a1
					,sum(operator_int*coalesce(ee.b1,0)) as b1
					,sum(operator_int*coalesce(ee.c1,0)) as c1
					,sum(operator_int*coalesce(ee.d1,0)) as d1
					,sum(operator_int*coalesce(ee.e1,0)) as e1
					,sum(operator_int*coalesce(ee.f1,0)) as f1
					,sum(operator_int*coalesce(ee.g1,0)) as g1
					,sum(operator_int*coalesce(ee.h1,0)) as h1
					,sum(operator_int*coalesce(ee.i1,0)) as i1
					,sum(operator_int*coalesce(ee.j1,0)) as j1
					,sum(operator_int*coalesce(ee.k1,0)) as k1
					,sum(operator_int*coalesce(ee.l1,0)) as l1
					,sum(operator_int*coalesce(ee.m1,0)) as m1
					,sum(operator_int*coalesce(ee.n1,0)) as n1
					,sum(operator_int*coalesce(ee.o1,0)) as o1
			from  account_report_format_designer_line_dd ee left join account_report_format_designer_line bb on ee.master_id=bb.id
		       where bb.master_id=master_id_t
		       group by ee.master_id
		       ) d
		 where a.llc=rec.llc
		   and d.master_id=a.id;

		update account_report_format_designer_line_dd a set  a1=coalesce(d.a1,0),
								     b1=coalesce(d.b1,0),
								     c1=coalesce(d.c1,0),
								     d1=coalesce(d.d1,0),
								     e1=coalesce(d.e1,0),
								     f1=coalesce(d.f1,0),
								     g1=coalesce(d.g1,0),
								     h1=coalesce(d.h1,0),
								     i1=coalesce(d.i1,0),
								     j1=coalesce(d.j1,0),
								     k1=coalesce(d.k1,0),
								     l1=coalesce(d.l1,0),
								     m1=coalesce(d.m1,0),
								     n1=coalesce(d.n1,0),
								     o1=coalesce(d.o1,0)
		  from  account_report_format_designer_line b left join account_report_format_designer c on b.master_id=c.id
			,(select t.* from account_report_format_designer_line t left join account_report_format_designer g on t.master_id=g.id
			   where g.id=master_id_t and t.llc=rec.llc) d
		 where a.master_id=b.id
		   and a.report_line_id is not null
		   and a.amount_from ='report_line'
		   and c.id=master_id_t
		   and b.llc=rec.llc-1
		   and a.report_line_id=d.id;

		   raise notice '%',rec.llc;

	end loop;
       ---3.计算比重 begin
	update account_report_format_designer_line a set a2=abs(round(coalesce(a.a1,0)/coalesce(b.a1,0),4))
	  from account_report_format_designer_line b
	 where a.percent_base_id=b.id
	   and a.percent_base_id is not null
	   and coalesce(b.a1,0)<>0
	   and a.master_id=master_id_t;

	update account_report_format_designer_line a set b2=abs(round(coalesce(a.b1,0)/coalesce(b.b1,0),4))
	  from account_report_format_designer_line b
	 where a.percent_base_id=b.id
	   and a.percent_base_id is not null
	   and coalesce(b.b1,0)<>0
	   and a.master_id=master_id_t;


	update account_report_format_designer_line a set b2=abs(round(coalesce(a.c1,0)/coalesce(b.c1,0),4))
	  from account_report_format_designer_line b
	 where a.percent_base_id=b.id
	   and a.percent_base_id is not null
	   and coalesce(b.c1,0)<>0
	   and a.master_id=master_id_t;

	update account_report_format_designer_line a set d2=abs(round(coalesce(a.d1,0)/coalesce(b.d1,0),4))
	  from account_report_format_designer_line b
	 where a.percent_base_id=b.id
	   and a.percent_base_id is not null
	   and coalesce(b.d1,0)<>0
	   and a.master_id=master_id_t;

	update account_report_format_designer_line a set e2=abs(round(coalesce(a.e1,0)/coalesce(b.e1,0),4))
	  from account_report_format_designer_line b
	 where a.percent_base_id=b.id
	   and a.percent_base_id is not null
	   and coalesce(b.e1,0)<>0
	   and a.master_id=master_id_t;

	update account_report_format_designer_line a set f2=abs(round(coalesce(a.f1,0)/coalesce(b.f1,0),4))
	  from account_report_format_designer_line b
	 where a.percent_base_id=b.id
	   and a.percent_base_id is not null
	   and coalesce(b.f1,0)<>0
	   and a.master_id=master_id_t;

	update account_report_format_designer_line a set g2=abs(round(coalesce(a.g1,0)/coalesce(b.g1,0),4))
	  from account_report_format_designer_line b
	 where a.percent_base_id=b.id
	   and a.percent_base_id is not null
	   and coalesce(b.g1,0)<>0
	   and a.master_id=master_id_t;

	update account_report_format_designer_line a set h2=abs(round(coalesce(a.h1,0)/coalesce(b.h1,0),4))
	  from account_report_format_designer_line b
	 where a.percent_base_id=b.id
	   and a.percent_base_id is not null
	   and coalesce(b.h1,0)<>0
	   and a.master_id=master_id_t;


	update account_report_format_designer_line a set i2=abs(round(coalesce(a.i1,0)/coalesce(b.i1,0),4))
	  from account_report_format_designer_line b
	 where a.percent_base_id=b.id
	   and a.percent_base_id is not null
	   and coalesce(b.i1,0)<>0
	   and a.master_id=master_id_t;

	update account_report_format_designer_line a set j2=abs(round(coalesce(a.j1,0)/coalesce(b.j1,0),4))
	  from account_report_format_designer_line b
	 where a.percent_base_id=b.id
	   and a.percent_base_id is not null
	   and coalesce(b.j1,0)<>0
	   and a.master_id=master_id_t;

	update account_report_format_designer_line a set k2=abs(round(coalesce(a.k1,0)/coalesce(b.k1,0),4))
	  from account_report_format_designer_line b
	 where a.percent_base_id=b.id
	   and a.percent_base_id is not null
	   and coalesce(b.k1,0)<>0
	   and a.master_id=master_id_t;


	update account_report_format_designer_line a set l2=abs(round(coalesce(a.l1,0)/coalesce(b.l1,0),4))
	  from account_report_format_designer_line b
	 where a.percent_base_id=b.id
	   and a.percent_base_id is not null
	   and coalesce(b.l1,0)<>0
	   and a.master_id=master_id_t;

	update account_report_format_designer_line a set m2=abs(round(coalesce(a.m1,0)/coalesce(b.m1,0),4))
	  from account_report_format_designer_line b
	 where a.percent_base_id=b.id
	   and a.percent_base_id is not null
	   and coalesce(b.m1,0)<>0
	   and a.master_id=master_id_t;

	update account_report_format_designer_line a set n2=abs(round(coalesce(a.n1,0)/coalesce(b.n1,0),4))
	  from account_report_format_designer_line b
	 where a.percent_base_id=b.id
	   and a.percent_base_id is not null
	   and coalesce(b.n1,0)<>0
	   and a.master_id=master_id_t;

	update account_report_format_designer_line a set o2=abs(round(coalesce(a.o1,0)/coalesce(b.o1,0),4))
	  from account_report_format_designer_line b
	 where a.percent_base_id=b.id
	   and a.percent_base_id is not null
	   and coalesce(b.o1,0)<>0
	   and a.master_id=master_id_t;
       ---计算比重 end
      /* 4.计算报表量值为不同报表项目比较 的数值
      --以下组出 类似语句:

	update account_report_format_designer_line a set a3=coalesce(a1,0)*(1)+coalesce(b1,0)*(-1)+coalesce(c1,0)*(1),
							 k3=coalesce(b1,0)*(1)+coalesce(c1,0)*(-1)+coalesce(a3,0)*(-1)
	 from account_report_format_designer b
	 where a.master_id=b.id and b.id=2；
      */
      for rec in select a.id,a.value_type as col
                   from account_report_format_designer_value a left join account_report_format_designer b on a.master_id=b.id
                  where b.id=master_id_t and substring(a.value_type,2,1)='3'--a3 b3 c3 ---
      loop
	  col_multi:='';
	  for rec_dd in select id from account_report_format_designer_value_dd where master_id=rec.id
	  loop
		      select 'coalesce('||b.value_type ||',0)' ||case when a.operator_int=-1 then '*(-1)' else '*(1)' end into col_1
			from account_report_format_designer_value_dd a  left join account_report_format_designer_value b on a.value_id=b.id
		       where a.id=rec_dd.id;
		       RAISE NOTICE 'col_15: % ,% ,%', col_1,char_length(col_1),CHAR_LENGTH(col_multi);
		       if char_length(col_1)>0 then
			   if CHAR_LENGTH(col_multi)>0 then
			       col_multi:=col_multi||'+'||col_1;
			   else
			       col_multi:=col_1;
			   end if;
		       else
			   col_multi:=col_1;
		       end if;
		       RAISE NOTICE 'col_multi6: %', col_multi;
	  end loop;

	  if char_length(col_multi)>0 then
	     if char_length(col_t)>0 then
		  col_t:=col_t||','||rec.col||'='|| col_multi;
	     else
		  col_t:=rec.col||'='|| col_multi;
	     end if;
	  else
	     col_t:=rec.col||'='|| col_multi;
	  end if;
	  RAISE NOTICE 'col_t7: %', col_t;
      end loop;
      RAISE NOTICE 'col_t8: %', col_t;

      if CHAR_LENGTH(col_t)>0 then
         sql:='update account_report_format_designer_line a set '|| col_t ||
              '  from account_report_format_designer b
                where a.master_id=b.id and b.id='||master_id_t||';';
         execute sql;
      end if;
      RAISE NOTICE 'sql: %', sql;

     con_name:='';
     col_t:='';
     for rec in select substring(a.value_type,1,1) pre
                  from account_report_format_designer_value a
                 where a.master_id=master_id_t
                 group by substring(a.value_type,1,1)
                 order by substring(a.value_type,1,1)
     loop
             con_name='';
             for rec_dd in  select id,value_type
			      from account_report_format_designer_value a
			     where substring(a.value_type,1,1)=rec.pre
			       and a.master_id=master_id_t
			     order by substring(a.value_type,2,1)
             loop
             RAISE NOTICE 'rec.pre ,value_type: % %',rec.pre, rec_dd.value_type;
		      select value_type||':'|| name into name_t
			from account_report_format_designer_value a
		       where a.id=rec_dd.id;

		       if char_length(con_name)>0 then
			   con_name:=con_name||','||name_t;
		       else
			   con_name:=name_t;
		       end if;
	      end loop;
              RAISE NOTICE 'con_name: %', con_name;
	      if char_length(con_name)>0 then
		      if char_length(col_t)>0 then
			 col_t:=col_t||','||rec.pre||'_name='''||con_name||'''';
		      else
			 col_t:=rec.pre||'_name='''||con_name||'''';
		      end if;
              end if;
     end loop;
      if CHAR_LENGTH(col_t)>0 then
         sql:='update account_report_format_designer_line a set '|| col_t ||
              '  from account_report_format_designer b
                where a.master_id=b.id and b.id='||master_id_t||';';
         execute sql;
      end if;
      RAISE NOTICE 'sql: %', sql;

      select  date_stop into end_date_t from account_period;
      select report_type into report_type_t from account_report_format_designer where id=master_id_t;
      RAISE NOTICE 'report_type: %', report_type_t;
      if report_type_t='balance' then--资产负债表
         PERFORM account_balance_sheet_write(end_date_t ,master_id_t,uid_t);
      end if;
      if report_type_t='income' then--利润表
         select id into tax_master_id_t from account_report_format_designer where report_type='tax';--税金表
         if coalesce(tax_master_id_t,0)>0 then
            PERFORM account_report_settle(period_id_t,tax_master_id_t,uid_t);
         end if;
         PERFORM account_income_statement_write(period_id_t ,master_id_t,tax_master_id_t,uid_t);
      end if;

      if report_type_t='cash' then--现金流量表
         PERFORM account_cash_flows_statement_write(period_id_t ,master_id_t,uid_t);
      end if;

      if report_type_t='tax_detail' then --应交税金明细表
         PERFORM account_tax_report_write(period_id_t ,master_id_t,uid_t);
      end if;

end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;



        """)
        self._cr.execute("""

                -- Function: compute_report_llc(integer)

                -- DROP FUNCTION compute_report_llc(integer);

                CREATE OR REPLACE FUNCTION compute_report_llc(report_id_t integer)
                  RETURNS void AS
                $BODY$
                    DECLARE
                    /*
                      由 计算报表项目  LLC
                      select compute_report_llc()

                    */
                        -- 声明段
                    BEGIN
                       create local temporary table if not exists tmp_report(id integer, llc integer);
                       insert into tmp_report( id, llc)
                       select id,0 as llc
                         from account_report_format_designer_line
                        where master_id=report_id_t;

                       create local temporary table if not exists tmp_parent_child (parent_id integer, child_id integer,llc integer);
                       insert into tmp_parent_child (parent_id, child_id,llc)

                    select a.master_id as parent_id,a.report_line_id as child_id,0 as llc
                      from account_report_format_designer_line_dd a  left join account_report_format_designer_line b on a.master_id=b.id
                                             left join account_report_format_designer c on b.master_id=c.id
                     where report_line_id is not null
                       and a.master_id is not null
                       and a.master_id<> a.report_line_id
                       and c.id=report_id_t;

                         WITH RECURSIVE LowerLevelCode(id, llc) AS (
                       SELECT id,max(llc)
                         FROM tmp_report a
                        group by id
                        UNION ALL
                       SELECT a.child_id,b.llc + 1
                         FROM tmp_parent_child a, LowerLevelCode b
                        WHERE a.parent_id= b.id
                          and b.llc<10
                        )
                       update tmp_report set LLC=( select Max(LLC) as LLc
                                                     from LowerLevelCode
                                                    group by id
                                                    having tmp_report.id= LowerLevelCode.id);
                       --select  LLC_wo,name_template,* from product_product order by LLC_wo;
                      update account_report_format_designer_line set llc=0 where master_id=report_id_t;
                      update account_report_format_designer_line a set llc=b.llc
                        from tmp_report b
                       where a.id=b.id
                         and a.master_id=report_id_t;

                      drop table tmp_parent_child;
                      drop table tmp_report;
                    END;
                    $BODY$
                  LANGUAGE plpgsql VOLATILE
                  COST 100;
        """)
